package tw.edu.ttu.cse.FourCLab;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class DatabaseOperation extends Activity {
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.database_operation);
        SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        Button createTableButton = (Button)findViewById(R.id.CreateNewTable);
        createTableButton.setOnClickListener(createNewTableClickListener);
        Button insertDataButton = (Button)findViewById(R.id.InsertData);
        insertDataButton.setOnClickListener(insertDataClickListener);
        Button deleteDataButton = (Button)findViewById(R.id.DeleteData);
        deleteDataButton.setOnClickListener(deleteDataClickListener);
        Button updateDataButton = (Button)findViewById(R.id.UpdataData);
        updateDataButton.setOnClickListener(updateDataClickListener);
        Button queryDataButton = (Button)findViewById(R.id.QueryData);
        queryDataButton.setOnClickListener(queryDataClickListener);
    }
    private OnClickListener createNewTableClickListener = new OnClickListener() {
        public void onClick(View v) {
        	SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        	TextView message = (TextView)findViewById(R.id.DatabaseMessage);
            try{
            	db.beginTransaction();
                db.execSQL("CREATE TABLE DATABASE_OPERATION(name STRING, no INTEGER);");
                db.setTransactionSuccessful();
                message.setText("Message : Create new table 'DATABASE_OPERATION' successful");
            }
            catch (Exception e){
            	message.setText("Message : Create new table 'DATABASE_OPERATION' fail");
                e.printStackTrace();
            }
            finally{
            	db.endTransaction();
            }
        }
    };
    
    private OnClickListener insertDataClickListener = new OnClickListener() {
        public void onClick(View v) {
        	SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        	TextView message = (TextView)findViewById(R.id.DatabaseMessage);
        	ContentValues insertData = new ContentValues();
        	insertData.put("name", "John");
        	insertData.put("no", 12345);
            try{
            	db.beginTransaction();
                db.insert("DATABASE_OPERATION", null, insertData);
                db.setTransactionSuccessful();
                message.setText("Message : Insert data successful");
            }
            catch (Exception e){
            	message.setText("Message : Insert data fail");
                e.printStackTrace();
            }
            finally{
            	db.endTransaction();
            }
        }
    };
    
    private OnClickListener deleteDataClickListener = new OnClickListener() {
        public void onClick(View v) {
        	SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        	TextView message = (TextView)findViewById(R.id.DatabaseMessage);
            try{
            	db.beginTransaction();
                db.delete("DATABASE_OPERATION", "name = 'John'", null);
                db.setTransactionSuccessful();
                message.setText("Message : Delete data successful");
            }
            catch (Exception e){
            	message.setText("Message : Delete data fail");
                e.printStackTrace();
            }
            finally{
            	db.endTransaction();
            }
        }
    };
    
    private OnClickListener updateDataClickListener = new OnClickListener() {
        public void onClick(View v) {
        	SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        	TextView message = (TextView)findViewById(R.id.DatabaseMessage);
        	ContentValues updateData = new ContentValues();
        	updateData.put("name", "Billy");
        	updateData.put("no", 67890);
            try{
            	db.beginTransaction();
                db.update("DATABASE_OPERATION", updateData, "name = 'John'", null);
                db.setTransactionSuccessful();
                message.setText("Message : Update data successful");
            }
            catch (Exception e){
            	message.setText("Message : Update data fail");
                e.printStackTrace();
            }
            finally{
            	db.endTransaction();
            }
        }
    };
    
    private OnClickListener queryDataClickListener = new OnClickListener() {
        public void onClick(View v) {
        	SQLiteDatabase db = openOrCreateDatabase("Practice",0,null);
        	TextView message = (TextView)findViewById(R.id.DatabaseMessage);
        	Cursor dataCursor = db.query("DATABASE_OPERATION", new String[]{"name","no"}, "name = 'Billy'", null, null, null, null);
        	if(dataCursor.getCount() > 0)
        	{
        		dataCursor.moveToFirst();
                message.setText("Message : Name = "+dataCursor.getString(0)+", no = "+dataCursor.getString(1));
            }
        	else{
            	message.setText("Message : No data found");
            }
        }
    };
}
